package com.liangyu.util;

import com.liangyu.common.exception.IaException;
import com.liangyu.common.exception.IaExceptionCode;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.internal.FileHelper;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

@Slf4j
public class excelUtils {

    private final static String excel2003L = ".xls";
    private final static String excel2007U = ".xlsx";

    /**
     * Logger
     */
    private static final Logger LOGGER = LoggerFactory.getLogger(FileHelper.class);

    /**
     * 文件未发现
     */
    private static final String FILE_NOT_FIND = "%s 文件不存在!";

    /**
     * 生成建表sql
     * @param filePath
     * @return String
     * @throws IOException
     */
    public static Map<String, Object> toSql(String filePath, int isPrimaryKey) throws IOException {
        Map<String, Object> map = new HashMap<>();
        String sqlStr = "";
        InputStream inputstream = new FileInputStream(filePath);
        Workbook wb = null;
        String fileType = filePath.substring(filePath.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            wb = new HSSFWorkbook(inputstream); //2003-
        } else if (excel2007U.equals(fileType)) {
            try {
                wb = new XSSFWorkbook(inputstream);
            } catch (Exception e) {
                inputstream = new FileInputStream(filePath);
                wb = new HSSFWorkbook(inputstream);  //2007+
            }
        } else {
            //关闭流
            if (inputstream != null) {
                inputstream.close();
            }
            throw new IaException(IaExceptionCode.WORKBOOK_ERROR);
        }
        Sheet sheet = null;
        Row row = null;
        int dataRowNumStart = wb.getNumberOfSheets();
        //获取所有sheet :wb.getNumberOfSheets()
        for (int sheetNumber = 0; sheetNumber < dataRowNumStart; sheetNumber++) {
            //第一个工作区间
            sheet = wb.getSheetAt(sheetNumber);
            //获取行：第二行；如果是空的这个sheet就是空的，跳过这个区间
            row = sheet.getRow(1);
            if (!StringUtil.isNullOrEmpty(row)) {
                //获取当前工作区间所有的总行数
                int lastRowNum = sheet.getLastRowNum();
                //数据库表的注释
                String tableNote = row.getCell(0).getStringCellValue();
                //数据库表名（第一行第一列）
                row = sheet.getRow(0);
                String tableName = row.getCell(0).getStringCellValue();
                //拼装创建语句开头
                String sqlBegin = "CREATE TABLE" + "`" +tableName+ "` (";
                //拼接语句结尾
                String sqlEnd = ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='"+tableNote+"';";
                //主键和索引
                String keyAndIndex = "";
                //中间的字段
                String sqlCentre = "";
                //获取填入的数据
                for(int i = 0; i < lastRowNum; i++) {
                    row = sheet.getRow(i+1);
                    //字段
                    row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);
                    String field = row.getCell(1).getStringCellValue();
                    if(StringUtil.isBlank(field)) {
                        log.debug("检测到的行数不对,已经没有数据,终止本次循环");
                        break;
                    }
                    if(i==0 && isPrimaryKey != 0) {
                        keyAndIndex = "PRIMARY KEY (`"+field+"`),KEY `PK_ID` (`"+field+"`)";
                    }
                    //数据类型
                    row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);
                    String type = row.getCell(2).getStringCellValue();
                    //长度
                    String dateLong = "";
                    if(!StringUtil.isNullOrEmpty(row.getCell(3))) {
                        row.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);
                        String s = row.getCell(3).getStringCellValue();
                        dateLong = "("+ StringUtils.strip(s,"()") +")";
                    }
                    //默认
                    String defaultVar = "";
                    if(!StringUtil.isNullOrEmpty(row.getCell(4))) {
                        row.getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);
                        defaultVar = "'"+row.getCell(4).getStringCellValue()+"'";
                    }else defaultVar = "NULL";
                    //注释
                    row.getCell(5, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);
                    String comment = row.getCell(5).getStringCellValue();
                    //拼接中间的内容
                    if(i==0) {
                        sqlCentre = sqlCentre + "`"+field+"`" + type + dateLong+ " NOT NULL AUTO_INCREMENT COMMENT '" + comment +"',";
                    }else {
                        sqlCentre = sqlCentre + "`" + field + "`" + type + dateLong + " DEFAULT " + defaultVar + " COMMENT '" + comment + "',";
                    }
                }
                sqlStr ="DROP TABLE IF EXISTS `"+tableName+"`;  " + sqlBegin + sqlCentre + keyAndIndex + sqlEnd;
                System.out.println("\""+tableName+"\":"+sqlStr);
                map.put(tableName,sqlStr);
            }
        }
        //4、关闭流
        if (inputstream != null) {
            inputstream.close();
        }
        //5、删除导入文件
        //FileHelper.deleteFile(filePath);
        deleteFile(filePath);
        return map;
    }
    /**
     * 删除单个文件
     *
     * @param fileName 被删除的文件名
     * @return 如果删除成功 ，则返回true，否则返回false
     */
    public static boolean deleteFile(String fileName) {
        File file = new File(fileName);
        if (file.exists() && file.isFile()) {
            boolean delete = file.delete();
            if (delete) {
                LOGGER.debug("删除文件 {} 成功!", fileName);
                return true;
            } else {
                LOGGER.debug("删除文件 {} 失败!", fileName);
                return false;
            }
        } else {
            LOGGER.debug(String.format(FILE_NOT_FIND, fileName));
            return true;
        }
    }
}
